﻿using System;
using System.Collections;
using System.Data;
using Model;
using System.Collections.Generic;


public class DirectService
{
    private readonly ConnectionBase sgbdr;
    public DirectService()
    {
      sgbdr= new ConnectionBase();
    }
    public DataTable GetSoftwareDisplayed(string whereclause, string orderclause)
    {
        string sql = "select * from SOFTWARE s inner join GROUPESOFTWARE gs on gs.GROUPESOFTWAREID=s.GROUPESOFTWAREID";
        sql += whereclause + " " + "Order by s.Software";
      
        return sgbdr.consultSQL(sql).Tables[0];

    }
    public void UpdateSoftwares(int softwareId, string software, string description, string version)
    {

        string sql = "update software set software=" + "'" + software + "'" +
        "," + "Description=" + "'" + description + "'" + "," + "Version=" + "'" + version + "'" + "where SoftwareId=" + softwareId;
        sgbdr.executeSQL(sql);

    }
    public void UpdateSoftware(int softwareId, string software, string description, string version)
    {

        string sql = "update software set software=" + "'" + software + "'" +
        "," + "Description=" + "'" + description + "'" + "," + "Version=" + "'" + version + "'" + "where SoftwareId=" + softwareId;
        sgbdr.executeSQL(sql);

    }
    public void UpdateSoftware(int softwareId, string software, int groupesoftwareId, string description, string version)
    {

        string sql = "update software set software=" + "'" + software + "'" + "," + "GroupeSoftwareId=" + groupesoftwareId +
        "," + "Description=" + "'" + description + "'" + "," + "Version=" + "'" + version + "'" + "where SoftwareId=" + softwareId;
        sgbdr.executeSQL(sql);

    }
    public void DeleteSoftware(int softwareId)
    {
        string sql = "delete from Software where SoftwareId=" + softwareId;
        sgbdr.executeSQL(sql);
    }
    public void SaveSoftware(string software, string description, string version)
    {
        string sql = "insert into software(SOFTWARE,DESCRIPTION,version) values(software,description,version)";
        sgbdr.executeSQL(sql);
    }
    public DataTable GetEquipementDisplayed(string whereclause , string orderbyClause)
    {
        // display equipment type where is not system whose id  = 1
        string sql = 
            "SELECT  EQUIPEMENT.EquipmentId, EQUIPEMENT.CODENUMBER, SYSTEM.SYSTEM, TYPEEQUIPMENT.TYPEEQUIPMENT, MODEL.MODEL, EQUIPEMENT.DEMAGED, " +
            "EMPLOYEE.EMPLOYEENAME,  CONVERT(varchar, EQUIPMENTPURCHASEINFORMATION.PURCHASEDATE, 101) AS PURCHASEDATE, COMPANYCONCERNING.COMPANYNAME " +
            "FROM EQUIPMENTPURCHASEINFORMATION INNER JOIN " +
            "EQUIPEMENT ON EQUIPMENTPURCHASEINFORMATION.PURCHASEID = EQUIPEMENT.PURCHASEID AND " +
            "EQUIPMENTPURCHASEINFORMATION.PURCHASEID = EQUIPEMENT.PURCHASEID INNER JOIN " +
            "HAVE ON EQUIPMENTPURCHASEINFORMATION.haveID = HAVE.haveID INNER JOIN " +
            "SYSTEM ON HAVE.systemID = SYSTEM.SYSTEMID INNER JOIN " +
            "TYPEEQUIPMENT ON HAVE.typeEquipementID = TYPEEQUIPMENT.TYPEEQUIPEMENTID INNER JOIN " +
            "MODEL ON EQUIPEMENT.MODELID = MODEL.MODELID INNER JOIN " +
            "COMPANYCONCERNING ON EQUIPMENTPURCHASEINFORMATION.COMPANYID = COMPANYCONCERNING.COMPANYID INNER JOIN " +
            "OWN ON EQUIPEMENT.CODENUMBER = OWN.CODENUMBER INNER JOIN " +
            "EMPLOYEE ON OWN.EMPLOYEEID = EMPLOYEE.EMPLOYEEID " +
            " INNER JOIN LOCATION ON OWN.LOCATIONID = LOCATION.LOCATIONID "+
            "WHERE OWN.UNUSE  = 0 AND TypeEquipment.TypeEquipementID  != 1 ";
                
        
        sql += whereclause + "" + orderbyClause;
        return sgbdr.consultSQL(sql).Tables[0];
         
    }
    public void DeleteEquipmentType(int equipmentTypeId)
    {
        string sql = "delete from TypeEquipment where TypeEquipementId=" + equipmentTypeId;
        sgbdr.executeSQL(sql);
    }
    public DataTable GetGroupeSoftwareDisplayed(string whereclause, string orderclause)
    {
        string sql = string.Empty;
        sql = "select * from GROUPESOFTWARE";
        sql += whereclause + " " + "Order by GroupeSoftware.GroupeSoftware";
        
        return sgbdr.consultSQL(sql).Tables[0];
    }
    public DataTable GetEquipmentTypeBySystem(string equipementTypeId)
    {
        if (!String.IsNullOrEmpty(equipementTypeId))
        {
            string sql = "select * from System where SystemId in (select SystemId from HAVE where typeEquipementId=" +
                         equipementTypeId + ")";
            return sgbdr.consultSQL(sql).Tables[0];
        }
        return null;
    }
    public DataTable GetEquipmentTypeDisplayed(string whereclause, string orderclause)
    {
        string sql = "select * from TypeEquipment";
        sql += whereclause + " " + "Order by TypeEquipment.TypeEquipment";
        return sgbdr.consultSQL(sql).Tables[0];
    }
    public DataTable GetModelDisplayed(string whereclause, string orderclause)
    {
        string sql = "select * from MODEL";
        sql += whereclause + " " + "Order by Model.Model";
        return sgbdr.consultSQL(sql).Tables[0];
    }
    public  DataTable GetEmployeeCompanyDisplayed(string whereclause,string orderclause)
    {

        string sql = "select * from COMPANY";
        sql += whereclause + " " + "Order by Company.CompanyName";
        return sgbdr.consultSQL(sql).Tables[0];

    }
    public void  UpdateEmployeeCompany(int companyId,string companyName)
    {
        string sql = "update Company set CompanyName=" + "'" + companyName + "'" +
                     "where CompanyId=" + companyId;
        sgbdr.executeSQL(sql);

    }
   

    public DataTable GetSystemDisplayed(string whereclause, string orderclause)
    {
        string sql = "select * from SYSTEM";
        sql += whereclause + " " + "Order by System.System" ;
        return sgbdr.consultSQL(sql).Tables[0];
    }
    public DataTable GetDepartmentDisplayed(string whereclause,string orderclause)
    {

        string sql = "select * from DEPARTMENT";
        sql += whereclause + " " + "Order by DEPARTMENT.departmentName";
        return sgbdr.consultSQL(sql).Tables[0];

    }
   
    public DataTable GetOwnDiplayed(string whereclause,string orderclause)
    {

        string sql = "SELECT distinct OWN.CODENUMBER, OWN.BUILDINGID, OWN.LOCATIONID, BUILDING.BUILDING, LOCATION.LOCATION, EMPLOYEE.EMPLOYEENAME, "
                    + "OWN.EMPLOYEEID, EMPLOYEE.EMPLOYEENAME AS Expr1, OWN.unuse, "
                    + "DEPARTMENT.departmentName, TYPEEQUIPMENT.TYPEEQUIPMENT, MODEL.MODEL, SYSTEM.SYSTEM "
                    + "FROM OWN INNER JOIN "
                    + "EQUIPEMENT ON OWN.CODENUMBER = EQUIPEMENT.CODENUMBER INNER JOIN "
                    + "BUILDING ON BUILDING.BUILDINGID = OWN.BUILDINGID INNER JOIN "
                    + "LOCATION ON LOCATION.LOCATIONID = OWN.LOCATIONID INNER JOIN "
                    + "EMPLOYEE ON EMPLOYEE.EMPLOYEEID = OWN.EMPLOYEEID INNER JOIN "
                    + "DEPARTMENT ON DEPARTMENT.departmentID = EMPLOYEE.departmentID INNER JOIN "
                    + "MODEL ON MODEL.MODELID = EQUIPEMENT.MODELID INNER JOIN "
                    + "[IN] ON [IN].modelID = MODEL.MODELID INNER JOIN "
                    + "TYPEEQUIPMENT ON TYPEEQUIPMENT.TYPEEQUIPEMENTID = [IN].typeEquipementID INNER JOIN "
                    + "HAVE ON HAVE.typeEquipementID = TYPEEQUIPMENT.TYPEEQUIPEMENTID INNER JOIN "
                    + "SYSTEM ON SYSTEM.SYSTEMID = HAVE.systemID INNER JOIN "
                    + "EQUIPMENTPURCHASEINFORMATION ON HAVE.haveID = EQUIPMENTPURCHASEINFORMATION.haveID AND "
                    + "EQUIPEMENT.PURCHASEID = EQUIPMENTPURCHASEINFORMATION.PURCHASEID AND EMPLOYEE.EMPLOYEENAME<>'stock' and own.unuse=0 " ;
        sql += whereclause + "" + orderclause;
        return sgbdr.consultSQL(sql).Tables[0];
    }
   
    public DataTable GetHistoryEquipment(string codeNumber)
    {
        if(!string.IsNullOrEmpty(codeNumber))
        {
            string sql="SELECT OWN.CODENUMBER, CONVERT(VARCHAR,OWN.DATEENTER,101) AS DATEENTER, EMPLOYEE.EMPLOYEENAME"
				+ " FROM OWN INNER JOIN"
				+ " EMPLOYEE ON OWN.EMPLOYEEID = EMPLOYEE.EMPLOYEEID"
				+ " WHERE ((OWN.CODENUMBER ="+codeNumber+")) order by OWN.DATEENTER DESC, OWN.ordreReservation DESC";

            return sgbdr.consultSQL(sql).Tables[0];
        }
        return null;
    }
    public DataTable GetOwnerEquipment(string codeNumber)
    {
        if(!string.IsNullOrEmpty(codeNumber))
        {
            string sql = " SELECT CONVERT(varchar, COMPOSE.DATECREATION, 101) AS DATECREATION, TYPEEQUIPMENT.TYPEEQUIPMENT, MODEL.MODEL, "
				+ "SPAREPARTPURCHASEINFORMATION.CAPACITY, COMPOSE.CODENUMBER "
				+ "FROM COMPOSE INNER JOIN "
				+ "SPAREPART ON COMPOSE.SPAREPARTID = SPAREPART.SPAREPARTID INNER JOIN "
				+ "MODEL ON SPAREPART.MODELID = MODEL.MODELID INNER JOIN "
				+ "[IN] ON MODEL.MODELID = [IN].modelID INNER JOIN "
				+ "TYPEEQUIPMENT ON TYPEEQUIPMENT.TYPEEQUIPEMENTID = [IN].typeEquipementID INNER JOIN "
				+ "HAVE ON HAVE.typeEquipementID = TYPEEQUIPMENT.TYPEEQUIPEMENTID INNER JOIN "
				+ "SPAREPARTPURCHASEINFORMATION ON SPAREPART.INFORMATIONID = SPAREPARTPURCHASEINFORMATION.INFORMATIONID AND "
				+ "HAVE.haveID = SPAREPARTPURCHASEINFORMATION.haveID "
				+ "WHERE (COMPOSE.CODENUMBER="+codeNumber+")";
            return sgbdr.consultSQL(sql).Tables[0];
        }
        return null;
    }

    public DataTable GetOwnerByCodeNumbers(string codeNumber)
    {
        if (!String.IsNullOrEmpty(codeNumber))
        {
            string sql = "SELECT Convert(varchar,OWN.DATEENTER,101) AS DATEENTER FROM OWN WHERE OWN.DATEENTER >= ALL(SELECT OWN.DATEENTER FROM OWN WHERE CODENUMBER=" +codeNumber + ")";
                   
            return sgbdr.consultSQL(sql).Tables[0];
        }
        return null;
    }
    public DataTable GetOwnerByCodeNumber(string  codeNumber)
    {
        if(!string.IsNullOrEmpty(codeNumber))
        {
            string sql = "SELECT  distinct OWN.CODENUMBER,OWN.DATEENTER, OWN.BUILDINGID, OWN.LOCATIONID, BUILDING.BUILDING, LOCATION.LOCATION, EMPLOYEE.EMPLOYEENAME, "
                   + "OWN.EMPLOYEEID, EMPLOYEE.EMPLOYEENAME AS Expr1, OWN.unuse, "
                   + "DEPARTMENT.departmentName, TYPEEQUIPMENT.TYPEEQUIPMENT, MODEL.MODEL, SYSTEM.SYSTEM "
                   + "FROM OWN INNER JOIN "
                   + "EQUIPEMENT ON OWN.CODENUMBER = EQUIPEMENT.CODENUMBER INNER JOIN "
                   + "BUILDING ON BUILDING.BUILDINGID = OWN.BUILDINGID INNER JOIN "
                   + "LOCATION ON LOCATION.LOCATIONID = OWN.LOCATIONID INNER JOIN "
                   + "EMPLOYEE ON EMPLOYEE.EMPLOYEEID = OWN.EMPLOYEEID INNER JOIN "
                   + "DEPARTMENT ON DEPARTMENT.departmentID = EMPLOYEE.departmentID INNER JOIN "
                   + "MODEL ON MODEL.MODELID = EQUIPEMENT.MODELID INNER JOIN "
                   + "[IN] ON [IN].modelID = MODEL.MODELID INNER JOIN "
                   + "TYPEEQUIPMENT ON TYPEEQUIPMENT.TYPEEQUIPEMENTID = [IN].typeEquipementID INNER JOIN "
                   + "HAVE ON HAVE.typeEquipementID = TYPEEQUIPMENT.TYPEEQUIPEMENTID INNER JOIN "
                   + "SYSTEM ON SYSTEM.SYSTEMID = HAVE.systemID INNER JOIN "
                   + "EQUIPMENTPURCHASEINFORMATION ON HAVE.haveID = EQUIPMENTPURCHASEINFORMATION.haveID AND "
                   + "EQUIPEMENT.PURCHASEID = EQUIPMENTPURCHASEINFORMATION.PURCHASEID AND EMPLOYEE.EMPLOYEENAME<>'stock' and own.unuse=0  where OWN.CODENUMBER=" + codeNumber;
            return sgbdr.consultSQL(sql).Tables[0];
        }
        return null; 
    }
     
    public DataTable GetMessage() 
    {
        string sql = "select * from Message";
        return sgbdr.consultSQL(sql).Tables[0];

    }
    public void UpdateModel(int modelId, string model)
    {

        string sql = "update model set model=" + "'" + model + "'" + "where ModelId=" + modelId;
        sgbdr.executeSQL(sql);

    }
    public IList<EquipmentSystem> DataTableToIlistSystem(string whereClause, string orderbyClause)
    {
        DataTable dataTable = (GetSystemDisplayed(whereClause, orderbyClause));
        IList<EquipmentSystem> listItem = new List<EquipmentSystem>();
        foreach (DataRow row in dataTable.Rows)
        {
            EquipmentSystem system = new EquipmentSystem();
            system.SystemId = Int32.Parse(row["SYSTEMID"].ToString());
            system.SystemLabel = row["STSTEMLABEL"].ToString();
            listItem.Add(system);
        }
        return listItem;
        
    }
    
    public IList<EquipmentTmpObject> DataTableToIListEquipment(string whereClause, string orderbyClause)
    {
        DataTable dataTable = (GetEquipementDisplayed(whereClause, orderbyClause));
        IList<EquipmentTmpObject> listItem = new List<EquipmentTmpObject>();
        
        foreach (DataRow row in dataTable.Rows)
        {
            EquipmentTmpObject equip = new EquipmentTmpObject();
            equip.EquipmentId = Int32.Parse(row["EQUIPMENTID"].ToString());
            equip.CodeNumber = row["CODENUMBER"].ToString();
            equip.SystemLabel = row["SYSTEM"].ToString();
            equip.TypeLabel = row["TYPEEQUIPMENT"].ToString();
            equip.ModelLabel = row["MODEL"].ToString();
            equip.IsDemaged = (bool)row["DEMAGED"];
            equip.EmployeeName = row["EMPLOYEENAME"].ToString();
            equip.PurchaseDate = DateTime.Parse(row["PURCHASEDATE"].ToString());
            equip.PurchaseCompanyName = row["COMPANYNAME"].ToString();
            listItem.Add(equip);
        }
        return listItem;
    }
    public DataTable GetSuggestedCodeNumbers(int amountOfNumberSuggested)
    {
         int numberRequest;
         if (amountOfNumberSuggested != 0)
             numberRequest = amountOfNumberSuggested;
         else numberRequest = 0;

         string sql =
            " SELECT TOP " + numberRequest +
            "* FROM EquipNumSuggest WHERE SuggestedNumber NOT IN (SELECT CodeNumber FROM Equipement) " +
            "AND SuggestedNumber < (SELECT MAX(CONVERT(INTEGER, CodeNumber)) FROM Equipement) ";

        DataTable ds = sgbdr.consultSQL(sql).Tables[0];
        return ds;
    }
   public DataTable GetEmployee(string whereClause,string orderbyClause )
   {
       string sql = "select * from Employee ";
       sql += " " + whereClause + " " + orderbyClause;
       DataTable ds = sgbdr.consultSQL(sql).Tables[0];
       return ds;

    }

   public DataTable GetModelByTypeEquipmentId(int typeEquipmentId)
   {
       string query1 = "select  * FROM Model as m WHERE m.ModelId in (SELECT e.ModelId FROM [in] as e WHERE e.TypeEquipmentId = " +
           typeEquipmentId + ")";
       
       return sgbdr.consultSQL(query1).Tables[0];

   }
   public void UpdateSystem(int systemId, string system)
   {
       string sql = "update system set system = " + "'" + system + "'" + "where SystemId=" + systemId;
       sgbdr.executeSQL(sql);

   }
   public void UpdateEquipmentType(int TypeEquipementId, string TypeEquipment)
   {

       string sql = "update TypeEquipment set TypeEquipment=" + "'" +
       TypeEquipment + "'" + "where TypeEquipementId=" + TypeEquipementId;
       sgbdr.executeSQL(sql);

   }
   public void UpdateDepartment(int DepartmentId,string DepartmentName)
   {

     string sql="update Department set DepartmentName=" + "'" +
     DepartmentName+"'" + "where DepartmentId=" + DepartmentId;
     sgbdr.executeSQL(sql);

   }

   public DataTable GetEquipmentModelByType(int equipmentTypeId)
   {
       string sql = " select m.* from Model as m, [in] as comp  " +
                    "  where comp.typeEquipementId = " + equipmentTypeId +
                    " and comp.ModelId  = m.ModelId ";
       DataTable ds = sgbdr.consultSQL(sql).Tables[0];
       return ds;
   }
    public DataTable GetAvailableSparepartsInStock()


    {
        string sql = "select (sum(qty)-sum(nbout)) as modulus,typeequipment,sum(minimuminstock)/count(typeequipment) as minimuminstock,sum(MAXIMUMINSTOCK)/count(typeequipment) as Maximuminstock"
                       + " from sparepartpurchaseinformation inner join"
                       + " have on have.haveid = sparepartpurchaseinformation.haveid inner join"
                       + " typeequipment on typeequipment.typeequipementid=have.typeequipementid"
                       + " group by typeequipment";
       
        return sgbdr.consultSQL(sql).Tables[0];


    }
    public IList<SparepartPurchaseInfo> GetListAvailableSparepartsInStock()
    {
        DataTable dataTable = GetAvailableSparepartsInStock();
        IList<SparepartPurchaseInfo> listItem = new List<SparepartPurchaseInfo>();

        foreach (DataRow row in dataTable.Rows)
        {
            SparepartPurchaseInfo equip = new SparepartPurchaseInfo();
            // quantity here is quantity left in stock
            equip.Quantity = Int32.Parse(row["modulus"].ToString());
            equip.TypeLabel = row["typeequipment"].ToString();
            equip.MinQuantity = row["minimuminstock"] != null ? Int32.Parse(row["minimuminstock"].ToString()):0;
            equip.MaxQuantity = row["Maximuminstock"] != null ? Int32.Parse(row["Maximuminstock"].ToString()):0;
            listItem.Add(equip);
        }
        return listItem;
    }
    public DataTable GetSpareParts(string whereClause , string orderByClause)
    {
        string Sql = "SELECT SPAREPARTPURCHASEINFORMATION.INFORMATIONID, SYSTEM.SYSTEM AS system, TYPEEQUIPMENT.TYPEEQUIPMENT, TYPEEQUIPMENT.MINIMUMINSTOCK,TYPEEQUIPMENT.MAXIMUMINSTOCK, MODEL.MODEL, SPAREPARTPURCHASEINFORMATION.ISWARRANTY, "
                + "SPAREPARTPURCHASEINFORMATION.QTY, SPAREPARTPURCHASEINFORMATION.QTY - SPAREPARTPURCHASEINFORMATION.NBOUT AS balance, "
                + "SPAREPARTPURCHASEINFORMATION.NBOUT, SPAREPARTPURCHASEINFORMATION.NBDEMAGED, CONVERT(varchar, "
                + "SPAREPARTPURCHASEINFORMATION.PURCHASEDATE, 101) AS PURCHASEDATE, SPAREPART.SPAREPARTID "
                + "FROM SYSTEM INNER JOIN "
                + "HAVE ON HAVE.systemID = SYSTEM.SYSTEMID INNER JOIN "
                + "TYPEEQUIPMENT ON HAVE.typeEquipementID = TYPEEQUIPMENT.TYPEEQUIPEMENTID INNER JOIN "
                + "[IN] ON [IN].typeEquipementID = TYPEEQUIPMENT.TYPEEQUIPEMENTID INNER JOIN "
                + "MODEL ON [IN].modelID = MODEL.MODELID INNER JOIN "
                + "SPAREPART ON MODEL.MODELID = SPAREPART.MODELID INNER JOIN "
                + "SPAREPARTPURCHASEINFORMATION ON SPAREPART.INFORMATIONID = SPAREPARTPURCHASEINFORMATION.INFORMATIONID AND "
                + "HAVE.haveID = SPAREPARTPURCHASEINFORMATION.haveID INNER JOIN "
                + "COMPANYCONCERNING ON SPAREPARTPURCHASEINFORMATION.COMPANYID = COMPANYCONCERNING.COMPANYID";
        Sql += " " + whereClause + " " + orderByClause;

        return sgbdr.consultSQL(Sql).Tables[0];
    }
    public IList<SparepartTempObject> DataTableToIListSpareparts(string whereClause, string orderbyClause)
    {
        
        DataTable dataTable = (GetSpareParts(whereClause, orderbyClause));
        IList<SparepartTempObject> listItem = new List<SparepartTempObject>();
        int totalIn = 0;
        int totalOut = 0;
        int totalBalance = 0;
        foreach (DataRow row in dataTable.Rows)
        {
            SparepartTempObject tmpObject = new SparepartTempObject();
            tmpObject.SparepartId = Int32.Parse(row["SPAREPARTID"].ToString());
            tmpObject.PurchaseDate = row["PURCHASEDATE"] != null ? DateTime.Parse(row["PURCHASEDATE"].ToString()): DateTime.Parse("1/1/1753");
            tmpObject.NbDemaged = row["NBDEMAGED"].ToString() != null ? Int32.Parse(row["NBDEMAGED"].ToString()):0;
            tmpObject.Out = row["NBOUT"].ToString() != null ? Int32.Parse(row["NBOUT"].ToString()) :0 ;
            tmpObject.Balance = row["balance"].ToString() != null ? Int32.Parse(row["balance"].ToString()):0;
            tmpObject.In = row["QTY"] != null ? Int32.Parse( row["QTY"].ToString()):0;
            tmpObject.ModelLabel = row["MODEL"].ToString();
            tmpObject.Max = row["MAXIMUMINSTOCK"].ToString() != null ? Int32.Parse(row["MAXIMUMINSTOCK"].ToString()) :0; 
            tmpObject.Min = row["MINIMUMINSTOCK"].ToString() != null ? Int32.Parse(row["MINIMUMINSTOCK"].ToString()):0;
            tmpObject.TypeLabel = row["TYPEEQUIPMENT"].ToString();
            tmpObject.SystemLabel = row["system"].ToString();

            totalIn += tmpObject.In;
            totalOut += tmpObject.Out;
            totalBalance += tmpObject.Balance;

            tmpObject.TotalIn = totalIn;
            tmpObject.TotalOut = totalOut;
            tmpObject.TotalBalance = totalBalance;

            listItem.Add(tmpObject);
        }

        return listItem;
    }
    public DataTable GetEquipmentWarrantyAlert(string purchaseD)
    {
        string criteriaPlus = string.Empty;
        if (!String.IsNullOrEmpty(purchaseD))
        {

            criteriaPlus = " AND EQUIPMENTPURCHASEINFORMATION.DATEEND <= '" + purchaseD + "'";
        }

        string sql =
            " SELECT  EQUIPEMENT.EquipmentId, EQUIPEMENT.CODENUMBER, SYSTEM.SYSTEM, TYPEEQUIPMENT.TYPEEQUIPMENT,TYPEEQUIPMENT.ISSPAREPART, MODEL.MODEL, EQUIPEMENT.DEMAGED, EMPLOYEE.EMPLOYEENAME, " +
            " EQUIPMENTPURCHASEINFORMATION.POReferenceNumber," +
            " CONVERT(varchar, EQUIPMENTPURCHASEINFORMATION.PURCHASEDATE, 101) AS PURCHASEDATE, " +
            " EQUIPMENTPURCHASEINFORMATION.WarrantyPeriod,EQUIPMENTPURCHASEINFORMATION.UnitPrice,EQUIPMENTPURCHASEINFORMATION.DescriptionPurchase,EQUIPMENTPURCHASEINFORMATION.ContratDescription," +
            " COMPANYCONCERNING.COMPANYNAME FROM EQUIPMENTPURCHASEINFORMATION  INNER JOIN EQUIPEMENT ON EQUIPMENTPURCHASEINFORMATION.PURCHASEID = EQUIPEMENT.PURCHASEID AND EQUIPMENTPURCHASEINFORMATION.PURCHASEID = EQUIPEMENT.PURCHASEID " +


            " INNER JOIN HAVE ON EQUIPMENTPURCHASEINFORMATION.haveID = HAVE.haveID INNER JOIN SYSTEM ON HAVE.systemID = SYSTEM.SYSTEMID " +
            " INNER JOIN TYPEEQUIPMENT ON HAVE.typeEquipementID = TYPEEQUIPMENT.TYPEEQUIPEMENTID " +
            " INNER JOIN MODEL ON EQUIPEMENT.MODELID = MODEL.MODELID " +
            " INNER JOIN COMPANYCONCERNING ON EQUIPMENTPURCHASEINFORMATION.COMPANYID = COMPANYCONCERNING.COMPANYID " +
            " INNER JOIN OWN ON EQUIPEMENT.CODENUMBER = OWN.CODENUMBER " +
            " INNER JOIN EMPLOYEE ON OWN.EMPLOYEEID = EMPLOYEE.EMPLOYEEID WHERE OWN.UNUSE  = 1 AND EQUIPMENTPURCHASEINFORMATION.IsWarranty = 1 " +
            criteriaPlus;
       DataTable ds = sgbdr.consultSQL(sql).Tables[0];
       return ds;
   }
    public DataTable GetEquipmentWarrantyAlertBeforeMonth(int NumberOfMonthToAlert)
    {

        string sql = 
      " SELECT  EQUIPEMENT.EquipmentId, EQUIPEMENT.CODENUMBER, SYSTEM.SYSTEM, TYPEEQUIPMENT.TYPEEQUIPMENT,TYPEEQUIPMENT.ISSPAREPART, MODEL.MODEL, EQUIPEMENT.DEMAGED, EMPLOYEE.EMPLOYEENAME, " +
      " EQUIPMENTPURCHASEINFORMATION.POReferenceNumber," +
      " CONVERT(varchar, EQUIPMENTPURCHASEINFORMATION.PURCHASEDATE, 101) AS PURCHASEDATE, " +
      " EQUIPMENTPURCHASEINFORMATION.WarrantyPeriod,EQUIPMENTPURCHASEINFORMATION.UnitPrice,EQUIPMENTPURCHASEINFORMATION.DescriptionPurchase,EQUIPMENTPURCHASEINFORMATION.ContratDescription," +


      " COMPANYCONCERNING.COMPANYNAME FROM EQUIPMENTPURCHASEINFORMATION  INNER JOIN EQUIPEMENT ON EQUIPMENTPURCHASEINFORMATION.PURCHASEID = EQUIPEMENT.PURCHASEID AND EQUIPMENTPURCHASEINFORMATION.PURCHASEID = EQUIPEMENT.PURCHASEID " +
      " INNER JOIN (SELECT PurchaseID,DateDiff(month,DateAdd(MONTH, ISNULL(WarrantyPeriod, 0), PurchaseDate), GetDate()) NbDiff FROM EQUIPMENTPURCHASEINFORMATION WHERE YEAR(PurchaseDate) < 9999) B ON EQUIPMENTPURCHASEINFORMATION.PurchaseID = B.PurchaseID AND  B.NbDiff <= " +NumberOfMonthToAlert+" AND EQUIPMENTPURCHASEINFORMATION.IsWarranty = 1 " +

      " INNER JOIN HAVE ON EQUIPMENTPURCHASEINFORMATION.haveID = HAVE.haveID INNER JOIN SYSTEM ON HAVE.systemID = SYSTEM.SYSTEMID " +
      " INNER JOIN TYPEEQUIPMENT ON HAVE.typeEquipementID = TYPEEQUIPMENT.TYPEEQUIPEMENTID " +
      " INNER JOIN MODEL ON EQUIPEMENT.MODELID = MODEL.MODELID " +
      " INNER JOIN COMPANYCONCERNING ON EQUIPMENTPURCHASEINFORMATION.COMPANYID = COMPANYCONCERNING.COMPANYID " +
      " INNER JOIN OWN ON EQUIPEMENT.CODENUMBER = OWN.CODENUMBER " +
      " INNER JOIN EMPLOYEE ON OWN.EMPLOYEEID = EMPLOYEE.EMPLOYEEID WHERE OWN.UNUSE  = 1 ";
        DataTable ds = sgbdr.consultSQL(sql).Tables[0];
        return ds;
    }
    public DataTable GetEmployees(string whereClause )
    {
        string sql = "SELECT EMPLOYEE.EMPLOYEEID, GROUPEUSERS.NOMGROUPE, EMPLOYEE.EMPLOYEENAME, EMPLOYEE.EMPLOYEEEMAILADRESS,"
                     + "EMPLOYEE.USERNAME, DEPARTMENT.departmentName,EMPLOYEE.DEPARTMENTID FROM EMPLOYEE INNER JOIN"
                     + " GROUPEUSERS ON EMPLOYEE.IDGROUPE = GROUPEUSERS.IDGROUPE INNER JOIN"
                     + " DEPARTMENT ON EMPLOYEE.departmentID = DEPARTMENT.departmentID ";
        sql += whereClause +" Order by EMPLOYEENAME";
        DataTable ds = sgbdr.consultSQL(sql).Tables[0];
        return ds;
    }

    public DataTable GetSparepartWarrantyAlert(string purchaseD)
    {
        string criteriaPlus = string.Empty;
        if (!String.IsNullOrEmpty(purchaseD))
        {


            criteriaPlus = " AND SPAREPARTPURCHASEINFORMATION.DATEEND <= '" + purchaseD + "'";
        }

        string sql = "SELECT SPAREPARTPURCHASEINFORMATION.INFORMATIONID, SYSTEM.SYSTEM AS system, TYPEEQUIPMENT.TYPEEQUIPMENT, TYPEEQUIPMENT.MINIMUMINSTOCK,TYPEEQUIPMENT.MAXIMUMINSTOCK, MODEL.MODEL, SPAREPARTPURCHASEINFORMATION.ISWARRANTY, "
                + "SPAREPARTPURCHASEINFORMATION.QTY, SPAREPARTPURCHASEINFORMATION.QTY - SPAREPARTPURCHASEINFORMATION.NBOUT AS balance, "
                + "SPAREPARTPURCHASEINFORMATION.NBOUT, SPAREPARTPURCHASEINFORMATION.NBDEMAGED, CONVERT(varchar, "
                + "SPAREPARTPURCHASEINFORMATION.PURCHASEDATE, 101) AS PURCHASEDATE, SPAREPART.SPAREPARTID "
                + "FROM SYSTEM INNER JOIN "
                + "HAVE ON HAVE.systemID = SYSTEM.SYSTEMID INNER JOIN "
                + "TYPEEQUIPMENT ON HAVE.typeEquipementID = TYPEEQUIPMENT.TYPEEQUIPEMENTID INNER JOIN "
                + "[IN] ON [IN].typeEquipementID = TYPEEQUIPMENT.TYPEEQUIPEMENTID INNER JOIN "
                + "MODEL ON [IN].modelID = MODEL.MODELID INNER JOIN "
                + "SPAREPART ON MODEL.MODELID = SPAREPART.MODELID INNER JOIN "
                + "SPAREPARTPURCHASEINFORMATION ON SPAREPART.INFORMATIONID = SPAREPARTPURCHASEINFORMATION.INFORMATIONID AND "
                + "HAVE.haveID = SPAREPARTPURCHASEINFORMATION.haveID INNER JOIN "
                + "COMPANYCONCERNING ON SPAREPARTPURCHASEINFORMATION.COMPANYID = COMPANYCONCERNING.COMPANYID" +
            criteriaPlus;
        DataTable ds = sgbdr.consultSQL(sql).Tables[0];
        return ds;
    }
   
    
}
